﻿Public Class frmPayment
    Dim dsPayment As DataSet
    Dim daPayment As OleDb.OleDbDataAdapter
    Dim dsPayRs As DataSet
    Dim daPayRs As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim able1Comma, able2Comma, able3Comma, able4Comma As String

    Private Sub frmPayment_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub frmPayment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call gSubIconShow(Me)
        Call initForm()
        Call gSubMkCmbAccount(cmbAccount, "OT")
        Call gSubMkCmbPayMethod(cmbPayMethod)
        Call gSubMkCmbCompany("", cmbCompany)
        Call gSubMkCmbCompany("", cmbFindCompany)

        gSubMkCmbFlag(cmbFlag) '## 삭제 여부 콤보박스 초기화
        cmbFlag.SelectedIndex = 0
        gSubMkCmbFlag(cmbRsFlag) '## 삭제 여부 콤보박스 초기화
        cmbRsFlag.SelectedIndex = 0
        Call grid1Refresh("default")
        Call grid2Refresh("default")
        Call grid3Refresh("default")
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Call grid1Refresh("")

    End Sub

    Private Sub btnSearchReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchReset.Click
        txtSearch.Clear()
        'initForm()
        cmbFindCompany.SelectedValue = 0
        Call grid1Refresh("")
    End Sub

    Private Sub txtSearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtSearch.KeyPress
        If Asc(e.KeyChar) = 13 Then
            Call grid1Refresh("")
        End If
    End Sub

    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        Call txtReset()
    End Sub

    Private Sub txtFindCompany_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtFindCompany.KeyUp
        Call gSubMkCmbCompany(txtFindCompany.Text, cmbCompany)
    End Sub

    'Private Sub txtFindCompany_Leave(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtFindCompany.Leave
    '    Call gSubMkCmbCompany(txtFindCompany.Text, cmbCompany)
    'End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Call savePayment("showAnswer")
    End Sub


    Private Sub txtAmount_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAmount.Leave
        txtOrgAmount.Text = txtAmount.Text
        txtTax.Text = 0
    End Sub

    Private Sub txtAmount_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtAmount.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            able1Comma = ""
        Else
            able1Comma = "ok"
        End If
    End Sub

    Private Sub txtAmount_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtAmount.KeyUp
        If able1Comma = "ok" Then
            gSubWithComma(txtAmount)
        End If
    End Sub

    Private Sub txtOrgAmount_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtOrgAmount.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            able2Comma = ""
        Else
            able2Comma = "ok"
        End If
    End Sub

    Private Sub txtOrgAmount_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtOrgAmount.KeyUp
        If able2Comma = "ok" Then
            gSubWithComma(txtOrgAmount)
        End If
    End Sub

    Private Sub txtTax_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTax.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            able3Comma = ""
        Else
            able3Comma = "ok"
        End If
    End Sub

    Private Sub txtTax_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTax.KeyUp
        If able3Comma = "ok" Then
            gSubWithComma(txtTax)
        End If
    End Sub

    Private Sub txtRsAmount_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtRsAmount.KeyPress
        If Not Char.IsDigit(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.Handled = True
            able4Comma = ""
        Else
            able4Comma = "ok"
        End If
    End Sub

    Private Sub txtRsAmount_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtRsAmount.KeyUp
        If able4Comma = "ok" Then
            gSubWithComma(txtRsAmount)
        End If
    End Sub

    Private Sub btnSetTax_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSetTax.Click
        Dim valTax As Double
        If txtAmount.Text <> "" Then
            valTax = (CDbl(txtAmount.Text) / 11)
            txtTax.Text = valTax
            txtOrgAmount.Text = CDbl(txtAmount.Text) - CDbl(valTax)
            gSubWithComma(txtTax)
            gSubWithComma(txtOrgAmount)
        End If
    End Sub

    Private Sub btnRsSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRsSave.Click
        Call savePayResult()
    End Sub

    Private Sub btnAddPay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddPay.Click
        TabControl.SelectedIndex = 1
        Call txtReset()
    End Sub

    Private Sub btnAllSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAllSave.Click
        Call savePayment("allSave")
        txtRsAmount.Text = txtAmount.Text
        dpActionDt.Text = dpPlanDt.Text
        dpBankDt.Text = dpPlanDt.Text
        Call savePayResult()
    End Sub

    Private Sub grdSumCompany_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdSumCompany.CellClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdSumCompany.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If

        cmbFindCompany.SelectedValue = grdSumCompany.Rows(e.RowIndex).Cells(0).Value
        Call grid1Refresh("")
    End Sub

    Private Sub grdPayment_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdPayment.CellDoubleClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdPayment.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If

        '## 거래내역 상세 정보 호출
        Call paymentDetail(grdPayment.Rows(e.RowIndex).Cells(0).Value)

        '## 지급내역 그리드로 호출
        Call grid4Refresh()
    End Sub

    Private Sub cmbAccount_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbAccount.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbCompany_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbCompany.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbFindCompany_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbFindCompany.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbFlag_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbFlag.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbPayMethod_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbPayMethod.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbRsFlag_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbRsFlag.KeyPress
        e.Handled = True
    End Sub

#Region "프로시져"

    Private Function fnAddSql() As String
        Dim addQuery As String = ""
        If dpStartDt.Text <> "" Then
            addQuery = "AND A.planDt >= '" & dpStartDt.Text & "' "
        End If
        If dpEndDt.Text <> "" Then
            addQuery = addQuery + "AND A.planDt <= '" & dpEndDt.Text & "' "
        End If
        If txtSearch.Text <> "" Then
            addQuery = addQuery + " AND A.article LIKE '%" + txtSearch.Text + "%' "
        End If
        Return addQuery
    End Function

    Private Sub grid1Refresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""
        Dim valFindCompany As String = ""
        Dim totalAmt As Double = 0
        Dim totalOrgAmt As Double = 0
        Dim totalRestAmt As Double = 0
        Dim totalTax As Double = 0

        '## 검색 조건에 참여하는 쿼리는 함수로 별도 관리
        addSql = fnAddSql()
        If cmbFindCompany.SelectedValue.ToString <> "0" Then
            valFindCompany = cmbFindCompany.SelectedValue.ToString
        End If
        If valFindCompany <> "" Then
            If addSql <> "" Then
                addSql = addSql + " AND "
            End If
            addSql = addSql + "A.compCd=" + valFindCompany
        End If
        If initFlag = "default" Then
            addSql = " AND A.flag='N'"
        End If
        dsPayment = New DataSet

        sql = "SELECT AA.payCd, AA.planDt as 거래일, AA.article as 내역, AA.accountNm as 계정, AA.amount as 총액, AA.restAmount as 미지급금, AA.orgAmount as 원금, AA.tax as 부가세, CC.company as 거래처, AA.remarks as 메모 " & _
            "   FROM (" & _
            "       SELECT A.payCd, A.planDt, A.article, B.accountNm, A.amount, A.restAmount, A.orgAmount, A.tax, A.flag, A.compCd, A.remarks " & _
            "       FROM tblPayment A LEFT JOIN tblAccount B ON A.accountCd=B.accountCd " & _
            "       WHERE A.flag='Y'" + addSql & _
            "       ) AA LEFT JOIN tblCompany CC ON AA.compCd=CC.compCd " & _
            "    ORDER BY AA.planDt DESC "

        Try
            daPayment = New OleDb.OleDbDataAdapter(sql, dbConn)
            daPayment.Fill(dsPayment, "Payment")

            grdPayment.DataSource = dsPayment.Tables("Payment")
            lbGrid1Cnt.Text = "총 " + dsPayment.Tables("Payment").Rows.Count.ToString + " 건"

            For c = 0 To dsPayment.Tables("Payment").Rows.Count - 1
                totalAmt = totalAmt + CDbl(dsPayment.Tables("Payment").Rows(c).Item(4))
                totalOrgAmt = totalOrgAmt + CDbl(dsPayment.Tables("Payment").Rows(c).Item(6))
                totalTax = totalTax + CDbl(dsPayment.Tables("Payment").Rows(c).Item(7))
                totalRestAmt = totalRestAmt + CDbl(dsPayment.Tables("Payment").Rows(c).Item(5))
            Next
            lbTotalAmt.Text = "총합계 : " + Format(CInt(totalAmt), "#,##0") + "   /   원금합계 : " + Format(CInt(totalOrgAmt), "#,##0") + "   /   세금합계 : " + Format(CInt(totalTax), "#,##0")
            lbTotalRestAmt.Text = "미지급합계 : " + Format(CInt(totalRestAmt), "#,##0")

            dbConn.Close()
            Call setGrid1Design()

            If valFindCompany = "" Then
                Call grid2Refresh("")
                Call grid3Refresh("")
            End If
            'grdPayment.Columns(2).Frozen = True
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGrid1Design()

        grdPayment.Columns(0).Visible = False
        grdPayment.Columns(1).Width = "80"
        grdPayment.Columns(2).Width = "200"
        grdPayment.Columns(3).Width = "100"

        grdPayment.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdPayment.Columns(4).DefaultCellStyle.Format = "n0"
        grdPayment.Columns(4).Width = "80"
        grdPayment.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdPayment.Columns(5).DefaultCellStyle.Format = "n0"
        grdPayment.Columns(5).DefaultCellStyle.ForeColor = Color.Red
        grdPayment.Columns(5).Width = "80"
        grdPayment.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdPayment.Columns(6).DefaultCellStyle.Format = "n0"
        grdPayment.Columns(6).Width = "80"
        grdPayment.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdPayment.Columns(7).DefaultCellStyle.Format = "n0"
        grdPayment.Columns(7).Width = "80"
        grdPayment.Columns(8).Width = "150"
        grdPayment.Columns(9).Width = "150"
        With grdPayment.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter

        End With
        grdPayment.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        grdPayment.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        grdPayment.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        'grdPayment.Columns(2).Frozen = True

        gSubMkCmbFlag(cmbFlag) '## 삭제 여부 콤보박스 초기화
        cmbFlag.SelectedIndex = 0
    End Sub

    Private Sub grid2Refresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""

        '## 검색 조건에 참여하는 쿼리는 함수로 별도 관리
        addSql = fnAddSql()

        dsPayment = New DataSet

        If initFlag = "default" Then
            addSql = " AND A.flag='N'"
        End If
        sql = "SELECT A.compCd, B.company as 거래처, sum(A.amount) as 총금액, sum(A.restAmount) as 미지급총액, sum(A.orgAmount) as 원금총액, sum(A.tax) as 세금총액, B.compNo as 사업자번호, count(*) as 건수" + _
            "   FROM tblPayment a LEFT JOIN tblCompany B ON A.compCd=B.compCd " + _
            "       WHERE A.flag='Y' " + addSql + _
            "   GROUP BY  A.compCd, B.company, B.compNo ORDER BY B.company DESC "

        Try
            daPayment = New OleDb.OleDbDataAdapter(sql, dbConn)
            daPayment.Fill(dsPayment, "sumCompany")

            grdSumCompany.DataSource = dsPayment.Tables("sumCompany")

            dbConn.Close()
            Call setSumGridDesign(grdSumCompany)

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub grid3Refresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""

        '## 검색 조건에 참여하는 쿼리는 함수로 별도 관리
        addSql = fnAddSql()

        dsPayment = New DataSet
        If initFlag = "default" Then
            addSql = " AND A.flag='N'"
        End If

        sql = "SELECT A.accountCd, B.accountNm as 거래처, sum(A.amount) as 총금액, sum(A.restAmount) as 미지급총액, sum(A.orgAmount) as 원금총액, sum(A.tax) as 세금총액" + _
            "   FROM tblPayment a LEFT JOIN tblAccount B ON A.accountCd=B.accountCd " + _
            "       WHERE A.flag='Y' " + addSql + _
            "   GROUP BY A.accountCd, B.accountNm ORDER BY B.accountNm DESC "

        Try
            daPayment = New OleDb.OleDbDataAdapter(sql, dbConn)
            daPayment.Fill(dsPayment, "sumAccount")

            grdSumAccount.DataSource = dsPayment.Tables("sumAccount")

            dbConn.Close()
            Call setSumGridDesign(grdSumAccount)

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setSumGridDesign(ByVal obj As Object) '## 계정별합계와 거래처별 합계 공용으로 사용
        obj.Columns(0).Visible = False
        obj.Columns(1).Width = "120"

        obj.Columns(5).DefaultCellStyle.Format = "n0"
        obj.Columns(5).Width = "90"
        obj.Columns(2).DefaultCellStyle.Format = "n0"
        obj.Columns(2).Width = "90"
        obj.Columns(3).DefaultCellStyle.Format = "n0"
        obj.Columns(3).Width = "90"
        obj.Columns(3).DefaultCellStyle.ForeColor = Color.Red
        obj.Columns(4).DefaultCellStyle.Format = "n0"
        obj.Columns(4).Width = "90"

        With obj.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleRight

        End With
        obj.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        'obj.Columns(1).Frozen = True
    End Sub

    Private Sub grid4Refresh() '## 거래지급내역 그리드
        Call dbConnection()
        Dim addSql As String = ""
        Dim valPayCd As String

        If txtPayCd.Text = "" Then
            valPayCd = "0"
        Else
            valPayCd = txtPayCd.Text
        End If

        '## 검색 조건에 참여하는 쿼리는 함수로 별도 관리
        addSql = fnAddSql()

        dsPayRs = New DataSet

        sql = "SELECT A.payRsSeq, A.actionDt as 지급일, A.amount as 지급액, B.itemNm as 지급방법, A.bankDt as 통장일" + _
            "   FROM tblPayResult A LEFT JOIN (SELECT itemNm, itemCd FROM tblComCode WHERE flag='Y' AND groupCd='0006') B ON A.payMethod=B.itemCd " + _
            "       WHERE A.flag='Y' AND A.payCd=" + valPayCd + _
            "   ORDER BY A.actionDt DESC "

        Try
            daPayRs = New OleDb.OleDbDataAdapter(sql, dbConn)
            daPayRs.Fill(dsPayRs, "PayResult")

            grdPayResult.DataSource = dsPayRs.Tables("PayResult")

            dbConn.Close()
            Call setGrid4Design()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub


    Private Sub setGrid4Design() '## 계정별합계와 거래처별 합계 공용으로 사용
        grdPayResult.Columns(0).Visible = False

        grdPayResult.Columns(1).Width = "80"
        grdPayResult.Columns(2).DefaultCellStyle.Format = "n0"
        grdPayResult.Columns(2).Width = "90"
        grdPayResult.Columns(3).Width = "90"
        grdPayResult.Columns(4).Width = "80"

        With grdPayResult.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter

        End With
        grdPayResult.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
    End Sub

    Private Sub initForm()
        Call dbConnection()
        Dim curDt As String = Format(Now, "yyyy-MM-dd").ToString
        Dim divDt() As Object
        divDt = Split(curDt, "-")
        Dim curMnthFirst = divDt(0) + "-" + divDt(1) + "-01"

        dpStartDt.Text = curMnthFirst
        dpEndDt.Text = curDt
        dbConn.Close()
    End Sub

    Private Sub txtReset()
        txtPayCd.Clear()
        txtArticle.Clear()
        txtAmount.Clear()
        txtFindCompany.Clear()
        txtOrgAmount.Clear()
        txtTax.Clear()
        txtRemarks.Clear()
        Call gSubMkCmbCompany("", cmbCompany)
        cmbFlag.SelectedIndex = 0
        Call txtRsReset()
        Call grid4Refresh()
    End Sub

    Private Sub txtRsReset()
        txtRsAmount.Clear()
        txtPayRsSeq.Clear()
        txtRsRemarks.Clear()
        cmbRsFlag.SelectedIndex = 0

    End Sub

    '## 거래내역 상세정보
    Private Sub paymentDetail(ByVal payCd As String)

        sql = "SELECT payCd, article, accountCd, amount, orgAmount, tax, compCd, planDt, flag, remarks  FROM tblPayment WHERE payCd=" + payCd

        Call dbConnection()
        dsPayment = New DataSet
        daPayment = New OleDb.OleDbDataAdapter(sql, dbConn)
        daPayment.Fill(dsPayment, "paymentInfo")

        dbConn.Close()

        txtReset()

        TabControl.SelectedIndex = 1
        txtPayCd.Text = dsPayment.Tables("paymentInfo").Rows(0).Item(0)
        txtArticle.Text = dsPayment.Tables("paymentInfo").Rows(0).Item(1)

        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(2)) = False Then
            cmbAccount.SelectedValue = dsPayment.Tables("paymentInfo").Rows(0).Item(2)
        End If
        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(3)) = False Then
            txtAmount.Text = dsPayment.Tables("paymentInfo").Rows(0).Item(3)
            txtAmount.Text = Format(CInt(txtAmount.Text), "#,##0")
        End If
        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(4)) = False Then
            txtOrgAmount.Text = dsPayment.Tables("paymentInfo").Rows(0).Item(4)
            txtOrgAmount.Text = Format(CInt(txtOrgAmount.Text), "#,##0")
        End If
        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(5)) = False Then
            txtTax.Text = dsPayment.Tables("paymentInfo").Rows(0).Item(5)
            txtTax.Text = Format(CInt(txtTax.Text), "#,##0")
        End If
        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(6)) = False Then
            cmbCompany.SelectedValue = dsPayment.Tables("paymentInfo").Rows(0).Item(6)
        End If
        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(7)) = False Then
            dpPlanDt.Text = dsPayment.Tables("paymentInfo").Rows(0).Item(7)
        End If
        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(8)) = False Then
            cmbFlag.SelectedValue = dsPayment.Tables("paymentInfo").Rows(0).Item(8)
        End If
        If IsDBNull(dsPayment.Tables("paymentInfo").Rows(0).Item(9)) = False Then
            txtRemarks.Text = dsPayment.Tables("paymentInfo").Rows(0).Item(9)
        End If
    End Sub

    Private Sub savePayment(ByVal rsFlag As String)

        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim rtnMsg As String = ""
        Dim valPayCd, valCompCd, valArticle, valAmount, valOrgAmout, valTax, valAccountCd, valPlanDt, valFlag, valRemarks As String
        Dim curDt As DateTime = Now

        valCompCd = cmbCompany.SelectedValue.ToString
        valArticle = gFnChkVal(txtArticle.Text)
        valRemarks = gFnChkVal(txtRemarks.Text)
        valAmount = gFnChkVal(txtAmount.Text)
        valOrgAmout = gFnChkVal(txtOrgAmount.Text)
        valTax = gFnChkVal(txtTax.Text)
        valAccountCd = cmbAccount.SelectedValue.ToString
        valPlanDt = dpPlanDt.Text
        valPayCd = txtPayCd.Text
        valFlag = cmbFlag.SelectedValue.ToString

        If valArticle = "" Then
            MsgBox("내역을 바르게 입력해 주세요")
            txtArticle.Focus()
            dbConn.Close()
            Exit Sub
        End If
        If valAccountCd = "" Then
            MsgBox("계정을 바르게 선택해 주세요")
            cmbAccount.Focus()
            dbConn.Close()
            Exit Sub
        End If
        If valAmount = "" Then
            MsgBox("금액을 바르게 입력해 주세요")
            txtAmount.Focus()
            dbConn.Close()
            Exit Sub
        End If


        If txtPayCd.Text = "" Then
            valPayCd = fnGetPayCd()
            sql = "INSERT INTO tblPayment(payCd, accountCd, article, amount, orgAmount, tax, compCd, planDt, restAmount, remarks, addDt, flag) " + _
                "   VALUES(" + valPayCd + ",'" + valAccountCd + "','" + valArticle + "','" + valAmount + "','" + valOrgAmout + "','" + valTax + _
                "','" + valCompCd + "','" + valPlanDt + "','" + valAmount + "','" + valRemarks + "','" & curDt & "','" + valFlag + "')"
            rtnMsg = "매입 정보가 등록되었습니다"
        Else
            sql = "UPDATE tblPayment SET article='" + valArticle + "', accountCd='" + valAccountCd + "', amount='" + valAmount + "', orgAmount='" + valOrgAmout + "', tax='" + valTax + "', restAmount='" + valAmount + "',  compCd='" + valCompCd + "', planDt='" + valPlanDt + "', remarks='" + valRemarks + "', flag='" + valFlag + "', updateDt='" + curDt + "' WHERE payCd=" + valPayCd
            rtnMsg = "매입 정보가 수정되었습니다"
        End If

        Call dbConnection()
        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            dbConn.Close()
            If rsFlag = "showAnswer" Then
                MsgBox(rtnMsg)
                Call txtReset()
            ElseIf rsFlag = "allSave" Then
                Call paymentDetail(valPayCd)
            End If

        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
        If rsFlag = "showAnswer" Then
            Call recountRestAmount(valPayCd)
            Call grid1Refresh("")
        End If
    End Sub

    '## 신규 PayCd 구하기
    Private Function fnGetPayCd() As String
        Dim maxPayCd As String
        Call dbConnection()
        sql = "SELECT max(payCd)+1 as maxPayCd FROM tblPayment"
        dsPayment = New DataSet
        daPayment = New OleDb.OleDbDataAdapter(sql, dbConn)
        daPayment.Fill(dsPayment, "maxPayCd")

        dbConn.Close()
        maxPayCd = dsPayment.Tables("maxPayCd").Rows(0).Item(0)

        Return maxPayCd
    End Function

    '## 지급 내역 저장
    Private Sub savePayResult()
        '## 거래내역 저장 함수 호출
        Call savePayment("")

        Call dbConnection()
        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim rtnMsg As String = ""
        Dim valPayCd, valPayRsSeq, valRsAmount, valActionDt, valBankDt, valPayMethod, valRsRemarks, valRsFlag As String
        Dim curDt As DateTime = Now


        valPayMethod = cmbPayMethod.SelectedValue.ToString
        valRsRemarks = gFnChkVal(txtRsRemarks.Text)
        valPayCd = txtPayCd.Text
        valRsAmount = txtRsAmount.Text
        valActionDt = dpActionDt.Text
        valBankDt = dpBankDt.Text
        valPayRsSeq = txtPayRsSeq.Text
        valRsFlag = cmbRsFlag.SelectedValue.ToString

        If txtPayCd.Text = "" Then
            MsgBox("거래내역을 먼저 선택해 주세요")
            dbConn.Close()
            Exit Sub
        End If
        If txtRsAmount.Text = "" Then
            MsgBox("지급액을 바르게 입력해 주세요")
            txtRsAmount.Focus()
            dbConn.Close()
            Exit Sub
        End If


        If txtPayRsSeq.Text = "" Then
            sql = "INSERT INTO tblPayResult(payCd, amount, actionDt, bankDt, payMethod, remarks, flag, addDt) VALUES(" + valPayCd + ",'" + valRsAmount + "','" + valActionDt + "','" + valBankDt + "','" + valPayMethod + "','" + valRsRemarks + "','" + valRsFlag + "','" + curDt + "')"
            rtnMsg = "지급 정보가 등록되었습니다"
        Else
            sql = "UPDATE tblPayResult SET amount='" + valRsAmount + "', actionDt='" + valActionDt + "', bankDt='" + valBankDt + "', payMethod='" + valPayMethod + "', remarks='" + valRsRemarks + "', flag='" + valRsFlag + "', updateDt='" + curDt + "' WHERE payRsSeq=" + valPayRsSeq
            rtnMsg = "지급 정보가 수정되었습니다"
        End If

        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            dbConn.Close()
            MsgBox(rtnMsg)
        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try

        Call recountRestAmount(valPayCd)
        Call grid4Refresh()
        Call txtRsReset()
        Call grid1Refresh("")

    End Sub

    '## 지급 총액 적용(잔금-지급총액)
    Private Sub recountRestAmount(ByVal payCd As String)

        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim paidAmt As String

        paidAmt = fnGetPayRs(payCd)
        Call dbConnection()

        sql = "UPDATE tblPayment SET restAmount=(restAmount-" + paidAmt + ") WHERE payCd=" + payCd + " AND (restAmount-" + paidAmt + ")>=0"
        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        oleCmd.ExecuteNonQuery()
        oleTran.Commit()

        dbConn.Close()
    End Sub

    '## 지급 총액 구하기
    Private Function fnGetPayRs(ByVal payCd As String) As String
        Dim paidAmt As String
        Call dbConnection()
        sql = "SELECT sum(amount) as paidAmt FROM tblPayResult WHERE flag='Y' AND payCd=" + payCd + " GROUP BY payCd"
        dsPayment = New DataSet
        daPayment = New OleDb.OleDbDataAdapter(sql, dbConn)
        daPayment.Fill(dsPayment, "paidAmount")

        dbConn.Close()
        If dsPayment.Tables("paidAmount").Rows.Count > 0 Then
            paidAmt = dsPayment.Tables("paidAmount").Rows(0).Item(0)
        Else
            paidAmt = 0
        End If
        Return paidAmt
    End Function

    Private Sub btnSetPayResult_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSetPayResult.Click
        txtRsAmount.Text = txtAmount.Text
        dpActionDt.Text = dpPlanDt.Text
        dpBankDt.Text = dpPlanDt.Text
    End Sub

    Private Sub grdPayResult_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdPayResult.CellClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdPayResult.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If
        sql = "SELECT payRsSeq, amount, payMethod, actionDt, bankDt, remarks, flag  FROM tblPayResult WHERE payRsSeq=" & grdPayResult.Rows(e.RowIndex).Cells(0).Value

        Call dbConnection()
        dsPayment = New DataSet
        daPayment = New OleDb.OleDbDataAdapter(sql, dbConn)
        daPayment.Fill(dsPayment, "payResultInfo")

        dbConn.Close()

        txtRsReset()

        txtPayRsSeq.Text = dsPayment.Tables("payResultInfo").Rows(0).Item(0)
        txtRsAmount.Text = dsPayment.Tables("payResultInfo").Rows(0).Item(1)
        txtRsAmount.Text = Format(CInt(txtRsAmount.Text), "#,##0")

        If IsDBNull(dsPayment.Tables("payResultInfo").Rows(0).Item(2)) = False Then
            cmbPayMethod.SelectedValue = dsPayment.Tables("payResultInfo").Rows(0).Item(2)
        End If
        If IsDBNull(dsPayment.Tables("payResultInfo").Rows(0).Item(3)) = False Then
            dpActionDt.Text = dsPayment.Tables("payResultInfo").Rows(0).Item(3)
        End If
        If IsDBNull(dsPayment.Tables("payResultInfo").Rows(0).Item(4)) = False Then
            dpBankDt.Text = dsPayment.Tables("payResultInfo").Rows(0).Item(4)
        End If
        If IsDBNull(dsPayment.Tables("payResultInfo").Rows(0).Item(5)) = False Then
            txtRsRemarks.Text = dsPayment.Tables("payResultInfo").Rows(0).Item(5)
        End If
        If IsDBNull(dsPayment.Tables("payResultInfo").Rows(0).Item(6)) = False Then
            cmbRsFlag.SelectedValue = dsPayment.Tables("payResultInfo").Rows(0).Item(6)
        End If
    End Sub
#End Region

End Class